package firbi.base;

import firbi.base.com.BusStop;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


/**
 * This is a database access helper class tailored for the favorites feature. It sets up the basic operations for adding, deleting, and listing favorites.
 * It can list one favorite or all.  It can also modify/update favorites in the database. 
 * 
 * @author Jenn Hanson
 */
public class FavsDbAdapter {

    public static final String KEY_DEST_NAME = "dest_name";  //street location of the stop
    public static final String KEY_ROUTE_NAME = "route_name"; //bus route name (eg 71 or 43)
    public static final String KEY_ROUTEID = "route_id";  //unique id for the route
	public static final String KEY_STOPID = "stop_id";   //unique id for the bus stop
	public static final String KEY_COUNT = "access_count";  //number of times this favorite has been 'chosen' for lookup
    public static final String KEY_ROWID = "_id";  //unique id for the row/entity in the database
    public static final String KEY_STOPADDRESS = "stop_address";  //closest address of a bus stop
	
    public static final long FAV_ALREADY_IN_DB=-2;
    public static final long FAV_NOT_IN_DB=2;
    public static final long INTERNAL_ERROR=-1;
    public static final long OK=0;

	
	protected static final String DB_NAME = "justin";
    protected static final String DB_TABLE = "ivan";
    protected static final int DB_VERSION = 2;
    private final Context mCtx;
    private static final String TAG = "FavoritesDbAdapter";
	
    protected DatabaseHelper mFavsDbHelper;
    protected SQLiteDatabase mFavsDb;
	
    
    /**
     * make the sql command for creating the database
     */
    private static final String DB_CREATE = "create table "+ DB_TABLE +" (_id integer primary key autoincrement, dest_name text not null, route_name text not null,"+
	"route_id text not null, stop_id text not null, access_count integer not null, stop_address text not null);";

	
	
	
    private static class DatabaseHelper extends SQLiteOpenHelper {
		
		
        DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

		
		
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DB_CREATE);
        }

		
		
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVrsn, int newVrsn) {
            Log.w(TAG, "Upgrading database from version " + oldVrsn + " to " + newVrsn + ", which will destroy all old data");
			String sqlCmd = "DROP TABLE IF EXISTS " + DB_NAME;
            db.execSQL(sqlCmd);
            onCreate(db);
        }
    }

	
	
	
	
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public FavsDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the favorites database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public FavsDbAdapter open() throws SQLException {
        mFavsDbHelper = new DatabaseHelper(mCtx);
        mFavsDb = mFavsDbHelper.getWritableDatabase();
        return this;
    }
	
	
    
    public void close() {
        mFavsDbHelper.close();
    }

/**
 * Checks if a favorite is already in the database based off routeId and stopId combination match.
 * @param routeId the id of the route of the potential favorite
 * @param stopId the id of the stop of the potential favorite
 * @return boolean value, True == the combination already represents a favorite in the database;  False == the combination doesn't match any combination in the database
 */
	public boolean checkIfFav(String routeId, String stopId){
		Cursor mCursor = null;
		mCursor = mFavsDb.query(true, DB_TABLE, new String[] {KEY_ROWID,}, KEY_ROUTEID + "='" + routeId +"' AND "+KEY_STOPID + " = '"+stopId+"'", null, null, null, null, null);
					
        if (mCursor.getCount() != 0) {
            return true;
        }
		
        return false;
	}

	
    /**
     * Create a new favorite using the name, route id and bus stop id provided. If the favorite is
     * successfully created return the new rowId for that favorite, otherwise return
     * a -1 to indicate failure.
     * 
	 * @param name the name of the street location of the stop
     * @param routeId the route id of the favorite
     * @param stopId the bus stop id of the favorite
     * @return returns a number to reflect if the fav was added; 
     * non-negative integer = ok, it worked = row id for the new favorite
     * INTERNAL_ERROR = -1 = it wasn't in the database but the system had an internal error when adding it
     * FAV_ALREADY_IN_DB = -2 = the favorite is already in the db
     */
    public long createFav(String destName, String routeName, String routeId, String stopId) {

    	boolean check = checkIfFav(routeId, stopId);
    	if(check){
    		return FAV_ALREADY_IN_DB;
    	}
        BusStop b = BusStop.find(stopId);
        
        ContentValues initValues = new ContentValues();
        initValues.put(KEY_DEST_NAME, destName);
        initValues.put(KEY_ROUTE_NAME, routeName);
        initValues.put(KEY_ROUTEID, routeId);
		initValues.put(KEY_STOPID, stopId );
		initValues.put(KEY_COUNT, 1);
		if(b == null)
			initValues.put(KEY_STOPADDRESS,"NA");
		else
		 initValues.put(KEY_STOPADDRESS, b.getAddress());

        return mFavsDb.insert(DB_TABLE, null, initValues);
    }

	
    /**
     * Delete the favorite with the given rowId
     * 
     * @param rowId id of favorite to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteFav(long rowId) {
        return mFavsDb.delete(DB_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }
	
    /**
     * Delete all favorites from the table
     * @return true if deleted, false otherwise
     */
    public boolean deleteAllFavs() {
        return mFavsDb.delete(DB_TABLE, null, null) > 0;
    }
	/**
     * Delete the favorite with the given routeId and stopId
     * 
     * @param routeId id of favorite to delete
	 * @param stopId id of stop of favorite to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteFav(String routeId, String stopId) {		
        return mFavsDb.delete(DB_TABLE, KEY_ROUTEID + "=" + routeId + " AND " + KEY_STOPID + "=" + stopId , null) > 0;
    }
	
	

    
    /**
     * Return a Cursor over the list of all favorites in the database
     * 
     * @return Cursor over all favorites
     */
    public Cursor getAllFavs() {
        return mFavsDb.query(DB_TABLE, new String[] {KEY_ROWID, KEY_DEST_NAME, KEY_ROUTE_NAME, KEY_ROUTEID, KEY_STOPID, KEY_COUNT, KEY_STOPADDRESS}, null, null, null, null, null);
    }

	
	
	/**
     * Return a Cursor positioned at the favorite that matches the given rowId
     * 
     * @param rowId id of favorite to retrieve
     * @return Cursor positioned to matching favorite, if found
     * @throws SQLException if favorite could not be found/retrieved
     */	
    public Cursor getFav(long rowId) throws SQLException {

        Cursor mCursor = mFavsDb.query(true, DB_TABLE, new String[] {KEY_ROWID, KEY_DEST_NAME, KEY_ROUTE_NAME, KEY_ROUTEID, KEY_STOPID, KEY_COUNT, KEY_STOPADDRESS}, KEY_ROWID + "=" + rowId, null, null, null, null, null);
		
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
		
        return mCursor;
    }

	
	
	/**
     * Return a Cursor positioned at the favorite that matches the given routeId and stopId combination
     * 
     * @param routeId route id of the favorite to retrieve
	 * @param stopId bus stop id of the favorite to retrieve
     * @return Cursor positioned to matching favorite, if found
     * @throws SQLException if favorite could not be found/retrieved
	 */
	public Cursor fetchFav(String routeId, String stopId) throws SQLException {
		
        Cursor mCursor = mFavsDb.query(true, DB_TABLE, new String[] {KEY_ROWID, KEY_DEST_NAME, KEY_ROUTE_NAME, KEY_ROUTEID, KEY_STOPID, KEY_COUNT, KEY_STOPADDRESS}, KEY_ROUTEID + "=" + routeId + 
									   " AND "+KEY_STOPID + "=" + stopId , null, null, null, null, null);
		
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
		
        return mCursor;
    }
	
	
	
	
    /**
     * Update the favorite using the details provided. The favorite to be updated is
     * specified using the rowId, and it is altered to use the name, routeId, and stopId
     * values passed in
     * 
     * @param rowId id of favorite to update
     * @param name value to set favorite name to
     * @param routeId value to set favorite route id to
	 * @param stopId value to set favorite bus stop id to 
	 * @param count value to set favorite access count to
     * @return true if the favorite was successfully updated, false otherwise
     */
    public boolean updateFav(long rowId, String dest_name, String route_name, String routeId, String stopId, int count) {
        ContentValues args = new ContentValues();
        args.put(KEY_DEST_NAME, dest_name);
        args.put(KEY_ROUTE_NAME, route_name);
		args.put(KEY_ROUTEID, routeId);
		args.put(KEY_STOPID, stopId);
		args.put(KEY_COUNT, count);

        return mFavsDb.update(DB_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

